﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WPLDB.DA.Models;
using WPLDB.Utility.Extensions;
using WPLDB.SQL;
using WPLDB.DA.Interfaces.UI.Models;

namespace WPLDB.Utility.DB
{
    public static class SQLHelper
    {
        #region "Constant"
        private static string _sqlForeingKey = "SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName" +
                                        " FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id " +
                                        "WHERE  OBJECT_NAME(f.parent_object_id)  in ({0}) AND OBJECT_NAME (f.referenced_object_id)  in ({0})";

        #endregion

        #region "Variables"
        private static DataTable _dsForeignKey;
        #endregion

        #region "Properties"

        public static DataTable DataTableForeignKey
        {
            get
            {
                return _dsForeignKey;
            }
        }

        #endregion

        #region "Static Methods"

        /// <summary>
        /// This method give list of columns names and other details in a table  
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static List<ColumnModel> GetColumnInformation(DataSet ds)
        {
            List<ColumnModel> listOfColumns = new List<ColumnModel>();
            foreach (DataRow item in ds.Tables[1].Rows)
            {
                listOfColumns.Add(new ColumnModel
                {
                    Name = item["column_name"].ToString(),
                    Type = item["type"].ToString(),
                    IsNuallable = item["Nullable"].ToString().ToLower().Equals("yes") ? true : false
                });
            }

            string pkName = GetPrimaryKey(ds);

            if (null != pkName)
            {
                var pkModels = listOfColumns.Where(x => x.Name == pkName).ToList();
                if (null != pkModels && pkModels.Count > 0)
                    foreach (var item in pkModels)
                    {
                        item.IsPrimaryKey = true;
                    }
                else
                {
                    listOfColumns.Insert(0, new ColumnModel
                    {
                        Name = "autogenrecno",
                        Type = "Int32",
                        IsNuallable = false,
                        IsPrimaryKey = true
                    });
                }
            }


            return listOfColumns;
        }

        /// <summary>
        /// This method helps to get the column type 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="listOfColumns"></param>
        /// <returns></returns>
        private static List<ColumnModel> GetColumnType(DataTable dt, List<ColumnModel> listOfColumns)
        {

            foreach (DataColumn item in dt.Columns)
            {
                var columnModel = listOfColumns.Where(x => x.Name == item.ColumnName.ToString()).FirstOrDefault();
                if (null != columnModel)
                {
                    columnModel.Type = item.DataType.Name.ToString();

                }
            }

            return listOfColumns;
        }

        /// <summary>
        /// This method get the Primary key information for a table
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static string GetPrimaryKey(DataSet ds)
        {
            string column = null;

            if (ds.Tables.Count >= 5
                && ds.Tables[5] != null
                && ds.Tables[5].Columns.Contains("index_name")
                && ds.Tables[5].Rows.Count > 0)
            {
                column = ds.Tables[5].Rows[0]["index_keys"].ToString();
            }

            return column;

        }

        /// <summary>
        /// This method gets the collection of EntitySet information
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="listOfChosenTables"></param>
        /// <returns></returns>
        private static List<EntityKeyModel> GetEntitySetInformation(string tableName, List<IWPLDBString> listOfChosenTables = null)
        {
            var ListOfEntitySet = new List<EntityKeyModel>();

            if (null != DataTableForeignKey)
            {
                var dr = DataTableForeignKey.Select("ReferenceTableName LIKE '" + tableName + "'");
                if (null != dr && dr.Count() > 0)
                    foreach (var item in dr)
                    {
                        ListOfEntitySet.Add(new EntityKeyModel
                        {
                            EntityKey = item["ForeignKey"].ToString(),
                            EntityName = listOfChosenTables != null ?
                                            listOfChosenTables.Where(x => x.Original == item["TableName"].ToString()).FirstOrDefault() :
                                            new WPLDBString
                                            {
                                                Original = item["TableName"].ToString(),
                                                Singular = item["TableName"].ToString(),
                                                Plural = item["TableName"].ToString()
                                            },
                            OtherKey = item["ColumnName"].ToString(),
                            ThisKey = item["ReferenceColumnName"].ToString()
                        });
                    }
            }



            return ListOfEntitySet;
        }

        /// <summary>
        /// This method get the collection of Entity Ref Information
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="listOfChosenTables"></param>
        /// <returns></returns>
        private static List<EntityKeyModel> GetEntityRefInformation(string tableName, List<IWPLDBString> listOfChosenTables = null)
        {
            var ListOfEntityRef = new List<EntityKeyModel>();

            if (null != DataTableForeignKey)
            {
                var dr = DataTableForeignKey.Select("TableName LIKE '" + tableName + "'");
                if (null != dr && dr.Count() > 0)
                    foreach (var item in dr)
                    {
                        ListOfEntityRef.Add(new EntityKeyModel
                        {
                            EntityKey = item["ForeignKey"].ToString(),
                            EntityName = listOfChosenTables != null ?
                                            listOfChosenTables.Where(x => x.Original == item["ReferenceTableName"].ToString()).FirstOrDefault() :
                                            new WPLDBString
                                            {
                                                Original = item["ReferenceTableName"].ToString(),
                                                Singular = item["ReferenceTableName"].ToString(),
                                                Plural = item["ReferenceTableName"].ToString()
                                            },
                            ThisKey = item["ColumnName"].ToString(),
                            OtherKey = item["ReferenceColumnName"].ToString()
                        });
                    }
            }



            return ListOfEntityRef;
        }

        /// <summary>
        /// This method helps to get the Table Name
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static string GetTableName(DataSet ds)
        {
            string tableName = null;

            if (ds.Tables.Count > 1
               && ds.Tables[0] != null
               && ds.Tables[0].Rows.Count == 1)
                tableName = ds.Tables[0].Rows[0][0].ToString();


            return tableName;
        }

        /// <summary>
        /// This method helps to get the collection of tables  with  'Columns->types, primary key'
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="listOfChosenTables"></param>
        /// <returns></returns>
        public static List<TableModel> GetListOfTableEntity(string connectionString, List<IWPLDBString> listOfChosenTables)
        {
            dynamic listOfTableModel = new List<TableModel>();
            if (null != listOfChosenTables)
                foreach (var item in listOfChosenTables)
                    listOfTableModel.Add(SQLHelper.GetTable(connectionString, item, listOfChosenTables));

            return listOfTableModel;
        }

        /// <summary>
        /// This method give the Table information with Column details
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <param name="listOfChosenTables"></param>
        /// <returns></returns>
        public static TableModel GetTable(string connectionString, IWPLDBString tableName, List<IWPLDBString> listOfChosenTables)
        {
            TableModel model = new TableModel();
            DataSet ds = null;
            string sql = "sp_help [{0}]; select * from [{0}] where 1=2;";

            dynamic sqlOut = (new SQLOperations(connectionString)).ExecuteCommand(string.Format(sql, tableName.Original));

            if (sqlOut.Error == null)
                ds = sqlOut.Result;

            if (null != ds
                && ds.Tables.Count > 0)
            {

                model.Name = tableName;

                model.ListOfColumns = GetColumnType(ds.Tables[ds.Tables.Count - 1], GetColumnInformation(ds));

                model.ListOfEntitySet = GetEntitySetInformation(model.Name.Original, listOfChosenTables);

                model.ListOfEntityRef = GetEntityRefInformation(model.Name.Original, listOfChosenTables);

            }

            return model;
        }

        /// <summary>
        /// This method give the Table information with Column details
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static TableModel GetTable(string connectionString, IWPLDBString tableName)
        {
            TableModel model = new TableModel();
            DataSet ds = null;
            string sql = "sp_help '{0}'; select * from {0} where 1=2;";

            dynamic sqlOut = (new SQLOperations(connectionString)).ExecuteCommand(string.Format(sql, tableName.Original));

            if (sqlOut.Error == null)
                ds = sqlOut.Result;

            if (null != ds
                && ds.Tables.Count > 0)
            { 
                 
                model.Name = tableName;

                model.ListOfColumns = GetColumnType(ds.Tables[ds.Tables.Count - 1], GetColumnInformation(ds));

                model.ListOfEntitySet = GetEntitySetInformation(model.Name.Original);

                model.ListOfEntityRef = GetEntityRefInformation(model.Name.Original);

            }

            return model;
        }

        /// <summary>
        /// This method helps to get the collection of table names
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="listOfChosenTables"></param>
        /// <returns></returns>
        public static List<TableModel> GetListOfTableModels(string connectionString)
        {
            var listOfTableModels = new List<TableModel>();

            if (null != connectionString)
            {
                var listOfTableNames = GetListOfTableNames(connectionString);

                if (null != listOfTableNames)
                    foreach (var item in listOfTableNames)
                        listOfTableModels.Add(new TableModel { Name = item });
            }

            return listOfTableModels;
        }

        /// <summary>
        /// Execute SQL query and get the table for Foreign Key information
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="listOfTables"></param>
        public static void SetForeignKey(string connectionString, IList<IWPLDBString> listOfTables)
        {
            var strTables = "";
            if (null != listOfTables)
                foreach (var item in listOfTables)
                    strTables += "'" + item.Original + "',";

            strTables = strTables.RemoveLastChar();

            var sqlOut = (new SQLOperations(connectionString)).ExecuteCommand(string.Format(_sqlForeingKey, strTables));

            if (null == sqlOut.Error
              && sqlOut.Result != null
              && sqlOut.Result.Tables.Count == 1)
            {
                _dsForeignKey = sqlOut.Result.Tables[0];

            }
        }


        /// <summary>
        /// This method gives list of table names
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static IList<IWPLDBString> GetListOfTableNames(string connectionString)
        {
            var sqlOut = (new SQLOperations(connectionString)).ExecuteCommand("SELECT * FROM sys.tables order by name;");



            List<IWPLDBString> listOfTables = null;

            if (null == sqlOut.Error
                && sqlOut.Result != null
                && sqlOut.Result.Tables.Count == 1)
            {
                listOfTables = (from dr in sqlOut.Result.Tables[0].Select()
                                select new WPLDBString
                                {
                                    Original = dr[0].ToString(),
                                    Singular = dr[0].ToString().Singular(),
                                    Plural = dr[0].ToString().Plural()
                                }).ToList<IWPLDBString>();

                var listOfPlural = (from dr in sqlOut.Result.Tables[0].Select()
                                    select dr[0].ToString().Plural()).ToList();

                listOfTables = FilterTableNames(listOfTables, listOfPlural).ToList();
            }


            return listOfTables;
        }

        /// <summary>
        /// This method checks whether any plural/singular combination affect the table names
        /// </summary>
        /// <param name="listOfTableNames"></param>
        /// <param name="listOfTableNamesPlural"></param>
        /// <returns></returns>
        private static IList<IWPLDBString> FilterTableNames(IList<IWPLDBString> listOfTableNames, List<string> listOfTableNamesPlural)
        {
            foreach (var item in listOfTableNamesPlural)
            {
                if (1 < listOfTableNames.Where(x => x.Plural == item).Count())
                {
                    var listOfChanges = listOfTableNames.Where(x => x.Plural == item);
                    foreach (var i in listOfChanges)
                    {
                        i.Plural = i.Original;
                        i.Singular = i.Original;
                    }
                }
            }

            return listOfTableNames;
        }

        /// <summary>
        /// This method gets list of database 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static IList<string> GetListOfDabase(string connectionString)
        {
            var sqlOut = (new SQLOperations(connectionString)).ExecuteCommand("SELECT * FROM sys.Databases");

            dynamic listofDatabases = null;

            if (null == sqlOut.Error
                && sqlOut.Result != null
                && sqlOut.Result.Tables.Count == 1)
            {
                listofDatabases = (from dr in sqlOut.Result.Tables[0].Select()
                                   select dr[0].ToString()).ToList();

            }

            return listofDatabases;

        }

        /// <summary>
        /// This method forms the connection string using ConnectionModel.
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string GetConnectionString(ConnectionModel model)
        {

            string windowString = @"Data Source={0};Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework;";
            string serverString = @"Data Source={0};Persist Security Info=True;User ID={1};Password={2};";
            string databaseString = "Initial Catalog={0};";
            string connectionString = string.Empty;

            if (model.IsValid)
            {

                if (model.Mode == Authentication.Window)
                    connectionString = string.Format(windowString, model.DataSource);
                else
                    connectionString = string.Format(serverString, model.DataSource, model.Username, model.Password);

                if (!string.IsNullOrEmpty(model.Database))
                    connectionString += string.Format(databaseString, model.Database);

            }

            return connectionString;
        }


        #endregion
    }
}
